﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class SucursalesDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeSucursal(ByVal pCveSuc As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM sucursales " & _
                                            "WHERE CveSuc = ?;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("CveSuc", pCveSuc)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraSucursales(ByVal pCatSucursal As SucursalesBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO sucursales(IdEmpresa,NombreSuc, Calle, NumExt, NumInt, " & _
                                                                "Colonia, Localidad, Referencia, Municipio, Estado, " & _
                                                                "Pais, CP, Status, CveSuc, Telefono, Correo, CvePemex ) " & _
                                            "VALUES (@IdEmpresa,@NombreSuc,@Calle,@NumExt,@NumInt," & _
                                                    "@Colonia,@Localidad,@Referencia,@Municipio, @Estado, " & _
                                                    "@Pais, @CP, @Status, @CveSuc, @Telefono, @Correo, @CvePemex );"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdEmpresa", 0)
                    myComm.Parameters.AddWithValue("@NombreSuc", pCatSucursal.NombreSucursal)
                    myComm.Parameters.AddWithValue("@Calle", pCatSucursal.Calle)
                    myComm.Parameters.AddWithValue("@NumExt", pCatSucursal.NumExterior)
                    myComm.Parameters.AddWithValue("@NumInt", pCatSucursal.NumInterior)
                    myComm.Parameters.AddWithValue("@Colonia", pCatSucursal.Colonia)
                    myComm.Parameters.AddWithValue("@Localidad", pCatSucursal.Localidad)
                    myComm.Parameters.AddWithValue("@Referencia", pCatSucursal.Referencia)
                    myComm.Parameters.AddWithValue("@Municipio", pCatSucursal.Municipio)
                    myComm.Parameters.AddWithValue("@Estado", pCatSucursal.Estado)
                    myComm.Parameters.AddWithValue("@Pais", pCatSucursal.Pais)
                    myComm.Parameters.AddWithValue("@CP", pCatSucursal.CP)
                    myComm.Parameters.AddWithValue("@Status", "A")
                    myComm.Parameters.AddWithValue("@CveSuc", pCatSucursal.ClaveSucursal)
                    myComm.Parameters.AddWithValue("@Telefono", pCatSucursal.Telefono)
                    myComm.Parameters.AddWithValue("@Correo", pCatSucursal.Correo)
                    myComm.Parameters.AddWithValue("@CvePemex", pCatSucursal.ClavePemex)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaSucursales(ByVal pSucursal As SucursalesBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE sucursales " & _
                                            "SET NombreSuc = @NombreSuc, " & _
                                                "Calle = @Calle, " & _
                                                "NumExt = @NumExt, " & _
                                                "NumInt = @NumInt, " & _
                                                "Colonia = @Colonia, " & _
                                                "Localidad = @Localidad, " & _
                                                "Referencia = @Referencia, " & _
                                                "Municipio = @Municipio, " & _
                                                "Estado = @Estado, " & _
                                                "Pais = @Pais, " & _
                                                "CP = @CP, " & _
                                                "Status = @Status, " & _
                                                "Correo = @Correo, " & _
                                                "Telefono = @Telefono, " & _
                                                "CvePemex = @CvePemex " & _
                                            "WHERE CveSuc = @CveSuc;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@NombreSuc", pSucursal.NombreSucursal)
                    myComm.Parameters.AddWithValue("@Calle", pSucursal.Calle)
                    myComm.Parameters.AddWithValue("@NumExt", pSucursal.NumExterior)
                    myComm.Parameters.AddWithValue("@NumInt", pSucursal.NumInterior)
                    myComm.Parameters.AddWithValue("@Colonia", pSucursal.Colonia)
                    myComm.Parameters.AddWithValue("@Localidad", pSucursal.Localidad)
                    myComm.Parameters.AddWithValue("@Referencia", pSucursal.Referencia)
                    myComm.Parameters.AddWithValue("@Municipio", pSucursal.Municipio)
                    myComm.Parameters.AddWithValue("@Estado", pSucursal.Estado)
                    myComm.Parameters.AddWithValue("@Pais", pSucursal.Pais)
                    myComm.Parameters.AddWithValue("@CP", pSucursal.CP)
                    Select Case pSucursal.Status
                        Case SucursalesBE.EstatusSucursal.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case SucursalesBE.EstatusSucursal.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "A")
                    End Select
                    'myComm.Parameters.AddWithValue("@IdEmpresa", pSucursal.IdEmpresa)
                    myComm.Parameters.AddWithValue("@Correo", pSucursal.Correo)
                    myComm.Parameters.AddWithValue("@Telefono", pSucursal.Telefono)
                    myComm.Parameters.AddWithValue("@CvePemex", pSucursal.ClavePemex)
                    myComm.Parameters.AddWithValue("@CveSuc", pSucursal.ClaveSucursal)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaEstatusPorClaveSucursal(ByVal pClaveSuc As String, ByVal pStatusActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE sucursales " & _
                                            "SET Status = @Status " & _
                                            "WHERE CveSuc = @CveSuc;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pStatusActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@CveSuc", pClaveSuc)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getSucursal(ByVal pCveSuc As String) As SucursalesBE
        Dim tmpSucursal As SucursalesBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdSuc, NombreSuc, Calle, NumExt, NumInt, Colonia, Localidad, " & _
                                                "Referencia, Municipio, Estado, Pais, CP, Status, CveSuc, Telefono, Correo, CvePemex " & _
                                            "FROM sucursales " & _
                                            "WHERE CveSuc = @CveSuc;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@CveSuc", pCveSuc)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpSucursal = SucursalesFact.Build(myDR)
                        Else
                            tmpSucursal = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpSucursal

        Catch ex As Exception
            Throw
        Finally
            tmpSucursal = Nothing
        End Try
    End Function

    Public Function getlistadoSucursales(ByVal strNombrSuc As String, ByVal tipoStatus As Enumeraciones.estatusParaBusquedas) As List(Of SucursalesBE)
        Dim SucursalesLs As List(Of SucursalesBE) = New List(Of SucursalesBE)
        Dim qryFiltro As String = ""
        Select Case tipoStatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                qryFiltro = ";"
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryFiltro = "AND Status ='A';"
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryFiltro = "AND Status ='I';"
        End Select

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdSuc, NombreSuc, Calle, NumExt, NumInt, Colonia, Localidad, " & _
                                                "Referencia, Municipio, Estado, Pais, CP, Status, CveSuc, Telefono, Correo, CvePemex " & _
                                           "FROM sucursales " & _
                                           "WHERE NombreSuc LIKE @NombrSuc " & _
                                                qryFiltro
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@NombrSuc", "%" & strNombrSuc & "%")
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Sucursales As SucursalesBE = SucursalesFact.Build(myDR)
                            If (Not Sucursales Is Nothing) Then
                                SucursalesLs.Add(Sucursales)
                            End If
                            Sucursales = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return SucursalesLs
        Catch ex As Exception
            Throw
        Finally
            SucursalesLs = Nothing
        End Try
    End Function

#End Region

    Public Function registraSucursales1(ByVal pSucursales1 As Sucursales1BE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 14) As OdbcParameter

        Try

            instrSql = "INSERT INTO ""Sucursales1""(""Calle1"", ""Ciudad1"", ""Colonia1"", ""CP1"", ""Descripcion"",""Estado1"", ""IdEmpresa"", ""IdSucursal"", ""NumExt1"", ""NumInt1"", " & _
                                    """Pais1"", ""Serie"", ""Serie1"", ""Serie2"", ""Status"") " & _
                                        "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);"

            oInsParm(0) = New OdbcParameter("", pSucursales1.Calle1)
            oInsParm(1) = New OdbcParameter("", pSucursales1.Ciudad1)
            oInsParm(2) = New OdbcParameter("", pSucursales1.Colonia1)
            oInsParm(3) = New OdbcParameter("", pSucursales1.CP1)
            oInsParm(4) = New OdbcParameter("", pSucursales1.Descripcion)
            oInsParm(5) = New OdbcParameter("", pSucursales1.Estado1)
            oInsParm(6) = New OdbcParameter("", pSucursales1.IdEmpresa)
            oInsParm(7) = New OdbcParameter("", pSucursales1.IdSucursal)
            oInsParm(8) = New OdbcParameter("", pSucursales1.NumExt1)
            oInsParm(9) = New OdbcParameter("", pSucursales1.NumInt1)
            oInsParm(10) = New OdbcParameter("", pSucursales1.Pais1)
            oInsParm(11) = New OdbcParameter("", pSucursales1.Serie)
            oInsParm(12) = New OdbcParameter("", pSucursales1.Serie1)
            oInsParm(13) = New OdbcParameter("", pSucursales1.Serie2)
            oInsParm(14) = New OdbcParameter("", pSucursales1.Status)


            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function modificaSucursales1(ByVal pSucursales1 As Sucursales1BE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 16) As OdbcParameter
        Try
            instrSql = "UPDATE ""Sucursales1"" " & _
                                        "SET ""Calle1""=?, ""Ciudad1""=?, ""Colonia1""=?, ""CP1""=?, ""Descripcion""=?,""Estado1""=?, ""IdSucursal""=?, ""NumExt1""=?, ""NumInt1""=?, " & _
                                    """Pais1""=?, ""Serie""=?, ""Serie1""=?, ""Serie2""=?, ""Status""=? " & _
                                                "WHERE ""IdEmpresa"" = ?;"

            oInsParm(0) = New OdbcParameter("", pSucursales1.Calle1)
            oInsParm(1) = New OdbcParameter("", pSucursales1.Ciudad1)
            oInsParm(2) = New OdbcParameter("", pSucursales1.Colonia1)
            oInsParm(3) = New OdbcParameter("", pSucursales1.CP1)
            oInsParm(4) = New OdbcParameter("", pSucursales1.Descripcion)
            oInsParm(5) = New OdbcParameter("", pSucursales1.Estado1)
            oInsParm(7) = New OdbcParameter("", pSucursales1.IdSucursal)
            oInsParm(8) = New OdbcParameter("", pSucursales1.NumExt1)
            oInsParm(9) = New OdbcParameter("", pSucursales1.NumInt1)
            oInsParm(10) = New OdbcParameter("", pSucursales1.Pais1)
            oInsParm(11) = New OdbcParameter("", pSucursales1.Serie)
            oInsParm(12) = New OdbcParameter("", pSucursales1.Serie1)
            oInsParm(13) = New OdbcParameter("", pSucursales1.Serie2)
            oInsParm(14) = New OdbcParameter("", pSucursales1.Status)
            oInsParm(15) = New OdbcParameter("", pSucursales1.IdEmpresa)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    Return True
                End Using
            End Using

        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function getSucursales1(ByVal pIdEmpr As Integer, ByVal pStrSucursales1 As String) As List(Of Sucursales1BE)
        Dim Sucursales1Ls As List(Of Sucursales1BE) = New List(Of Sucursales1BE)
        Dim instrSql As String

        Try
            instrSql = "SELECT ""Calle1"", ""Ciudad1"", ""Colonia1"", ""CP1"", ""Descripcion"",""Estado1"", ""IdEmpresa"", ""IdSucursal"", ""NumExt1"", ""NumInt1"", " & _
                                    """Pais1"", ""Serie"", ""Serie1"", ""Serie2"", ""Status"" " & _
                                     "FROM ""Sucursales1"" " & _
                                    "WHERE ""IdEmpresa"" = ? " & _
                                        "AND ""Descripcion"" LIKE '%" & pStrSucursales1 & "%' ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pIdEmpr))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Sucursales1 As Sucursales1BE = Sucursales1Fact.Build(myDR)
                            If (Not Sucursales1 Is Nothing) Then
                                Sucursales1Ls.Add(Sucursales1)
                            End If
                            Sucursales1 = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return Sucursales1Ls
        Catch ex As Exception
            Throw
        Finally
            Sucursales1Ls = Nothing
            instrSql = Nothing
        End Try
    End Function
End Class
